<?php

/**
 * @author Laurent SITTLER <lolo.sittler@gmail.com>
 * @author Alexandre GOMEZ <gomez.alexandre02@gmail.com>
 * @version 0.1
 */
require_once 'Library/Tab.php';
require_once 'Library/ContentTab.php';
require_once 'User_Model.php';
require_once 'Library/User.php';
require_once 'Library/DB.php';
require_once 'Library/External_Database.php';
require_once 'Library/Culture.php';

/**
 * Informations Model Class
 * @todo Regarder les informations dont nous avons besoin
 */
class Informations_Model extends Model {

    /**
     * Constructor
     */
    function __construct() {
        parent::__construct();
        $this->m_bd = NULL;
    }

    /**
     * Get Tabs informations
     * @return Array<Tab>
     */
    public function GetTabs() {
        $mo_user = new User_Model();
        $cusr = $mo_user->GetUserById(Session::Get(Constants::$SESSION_cuser));
        $tabs = array();
        $rep = $this->db->prepare("SELECT * FROM RFA_INT_TAB ORDER BY TAB_ID");
        $rep->execute();
        while ($result = $rep->fetch()) {
            if ($result['TAB_ADMIN'] != 0 && $cusr->GetAdmin() == 1) {
                $tab = new Tab();
                $tab->SetTab($result['TAB_ID'], $result['TAB_NM'], $result['TAB_ICON'], $result['TAB_PAGE_ID']);
                array_push($tabs, $tab);
            } else if ($result['TAB_ADMIN'] == 0) {
                $tab = new Tab();
                $tab->SetTab($result['TAB_ID'], $result['TAB_NM'], $result['TAB_ICON'], $result['TAB_PAGE_ID']);
                array_push($tabs, $tab);
            }
        }
        $rep->closeCursor();
        return $tabs;
    }

    /**
     * Get content Tabs
     * @param array $tabs
     * @return Array<ContentTab>
     */
    public function GetContentTabs(Array $tabs) {
        $content = array();
        foreach ($tabs as $tab) {
            $rep = $this->db->prepare("SELECT * FROM RFA_INT_PAGE WHERE PG_ID LIKE :idPage");
            $rep->execute(Array(
                ":idPage" => $tab->GetPage()
            ));
            while ($result = $rep->fetch()) {
                $page = new ContentTab();
                $page->SetContent($result['PG_ID'], $result['PG_NM']);
                array_push($content, $page);
            }
        }
        $rep->closeCursor();
        return $content;
    }

    public function GetAllLogs() {
        $req = $this->db->prepare("SELECT  LOG_ID, to_char(LOG_DATE, '" . Culture::FormatDatePDO() . "') AS LOG_DATE, LOG_IP, LOG_MSG, LOG_USER_ID FROM APP_INT_LOG ORDER BY LOG_ID");
        $req->setFetchMode(PDO::FETCH_ASSOC);
        $req->execute();
        return $req->fetchAll();
    }

    /**
     * Get Dictionary Datas Informations
     * @todo Inform sql request and database dictionary
     */
    public function GetDictionaryData1() {
        $this->m_sqlMySQL = 'SELECT * FROM TABLESPACES';
        $this->m_sqlOracle = 'SELECT tablespace_name AS "TABLESPACE", status AS "STATUT", file_name AS "FICHIER(s)", online_status AS "STATUT" FROM DBA_DATA_FILES';
        $dbname = 'information_schema';
        $this->SetInfoDB($dbname);
        return $this->m_result->fetchAll();
    }

    public function GetDictionaryData2() {
        $this->m_sqlMySQL = 'SELECT * FROM FILES';
        $this->m_sqlOracle = 'SELECT tablespace_name AS "TABLESPACE (TEMP)", status AS "STATUT", file_name AS "FICHIER(s)" FROM DBA_TEMP_FILES';
        $dbname = 'information_schema';
        $this->SetInfoDB($dbname);
        return $this->m_result->fetchAll();
    }

    public function GetDictionaryData3() {
        $this->m_sqlMySQL = 'SELECT TABLE_NAME, AVG_ROW_LENGTH, MAX_DATA_LENGTH, DATA_FREE FROM PARTITIONS';
        $this->m_sqlOracle = 'SELECT tablespace_name AS "TABLESPACE",bytes AS "TAILLE TOTAL",user_bytes AS "LIBRE",bytes-user_bytes AS "UTILISE",ROUND((bytes-user_bytes)/(bytes/100),2) AS "% D\'UTILISATION" FROM DBA_DATA_FILES ORDER BY tablespace_name';
        $dbname = 'information_schema';
        $this->SetInfoDB($dbname);
        return $this->m_result->fetchAll();
    }

    public function GetDictionaryData4() {
        $this->m_sqlMySQL = '';
        $this->m_sqlOracle = 'SELECT tablespace_name AS "TABLESPACE",bytes AS "TAILLE TOTAL",user_bytes AS "LIBRE",bytes-user_bytes AS "UTILISE",ROUND((bytes-user_bytes)/(bytes/100),2) AS "% D\'UTILISATION" FROM DBA_TEMP_FILES ORDER BY tablespace_name';
        $dbname = 'information_schema';
        $this->SetInfoDB($dbname);
        return $this->m_result->fetchAll();
    }

    /**
     * Get Memory informations
     *  @todo Inform sql request and database memory
     */
    public function GetMemory() {
        $this->m_sqlMySQL = 'SHOW STATUS';
        $this->m_sqlOracle = 'SELECT * FROM v$sgainfo';
        // Optionnale dbname
        $dbname = 'mysql';
        $this->SetInfoDB($dbname);
        // Return All datas fetAll. If more data, PDO return an error. So return just m_result
        return $this->m_result->fetchAll();
    }

    /**
     * Get Parameters informations
     *  @todo Inform sql request and database parameters
     */
    public function GetParameters() {
        $this->m_sqlMySQL = 'SHOW variables';
        $this->m_sqlOracle = 'SELECT * FROM v$instance';
        // Optionnale dbname
        $this->SetInfoDB();
        // Return All datas fetAll. If more data, PDO return an error. So return just m_result
        return $this->m_result->fetchAll();
    }

    /**
     * Get Databases informations
     *  @todo Inform sql request and database Databases
     */
    public function GetDatabases() {
        $this->m_sqlMySQL = '';
        $this->m_sqlOracle = 'SELECT * FROM DBA_DB_LINKS';
        // Optionnale dbname
        $dbname = 'mysql';
        $this->SetInfoDB($dbname);
        // Return All datas fetAll. If more data, PDO return an error. So return just m_result
        return $this->m_result->fetchAll();
    }

    /**
     * Get user informations
     */
    public function GetUsers() {
        $this->m_sqlMySQL = 'SELECT * FROM USER';
        $this->m_sqlOracle = 'SELECT username AS "UTILISATEUR", created AS "DATE DE CREATION", account_status AS "STATUT", default_tablespace AS "TBS DEFAUT", temporary_tablespace AS "TBS TEMPORAIRE" FROM DBA_USERS';
        // Optionnale dbname
        $dbname = 'mysql';
        $this->SetInfoDB($dbname);
        // Return All datas fetAll. If more data, PDO return an error. So return just m_result
        return $this->m_result->fetchAll();
    }

    public function GetUsersObjects() {
        $this->m_sqlMySQL = '';
        $this->m_sqlOracle = 'SELECT owner AS "UTILISATEUR", object_type AS "TYPE D\'OBJECT", count(object_type) AS "TOTAL" FROM DBA_OBJECTS GROUP BY owner, object_type ORDER BY owner, object_type';
        // Optionnale dbname
        $dbname = 'mysql';
        $this->SetInfoDB($dbname);
        // Return All datas fetAll. If more data, PDO return an error. So return just m_result
        return $this->m_result->fetchAll();
    }

    /**
     * Get Admin Scripts informations
     *  @todo Inform sql request and database Admin Scripts
     */
    public function GetScriptsAdmin() {
        $this->m_sqlMySQL = '';
        $this->m_sqlOracle = '';
        $this->SetInfoDB();
        return $this->m_result;
    }

    /**
     * Get Objetcs informations
     *  @todo Inform sql request and database Objects
     */
    public function GetObjects() {
        $this->m_sqlMySQL = '';
        $this->m_sqlOracle = 'SELECT owner AS "UTILISATEUR", object_type AS "TYPE D\'OBJECT", count(object_type) AS "TOTAL" FROM DBA_OBJECTS WHERE owner=\'' . strtoupper($_SESSION[Constants::$SESSION_ldb]) . '\' GROUP BY owner, object_type ORDER BY owner, object_type';
        // Optionnale dbname
        $dbname = 'mysql';
        $this->SetInfoDB($dbname);
        // Return All datas fetAll. If more data, PDO return an error. So return just m_result
        if (!empty($this->m_sqlMySQL))
            return $this->m_result->fetchAll();
        else
            return array();
    }

    public function GetObjectsDetails() {
        $this->m_sqlMySQL = '';
        $this->m_sqlOracle = 'SELECT object_name,object_type FROM USER_OBJECTS';
        // Optionnale dbname
        $dbname = 'mysql';
        $this->SetInfoDB($dbname);
        // Return All datas fetAll. If more data, PDO return an error. So return just m_result
        if (!empty($this->m_sqlMySQL))
            return $this->m_result->fetchAll();
        else
            return array();
    }

    /**
     * Get Tables informations
     *  @todo Inform sql request and database Tables
     */
    public function GetTables() {
        $this->m_sqlMySQL = 'SELECT * FROM TABLES WHERE table_type = \'BASE TABLE\'';
        $this->m_sqlOracle = 'SELECT * FROM all_tables WHERE owner=\'' . strtoupper($_SESSION[Constants::$SESSION_ldb]) . '\' ORDER BY table_name';
        // Optionnale dbname
        $dbname = 'information_schema';
        $this->SetInfoDB($dbname);
        // Return All datas fetAll. If more data, PDO return an error. So return just m_result
        return $this->m_result->fetchAll();
    }

    /**
     * 
     * @return type
     */
    public function GetTablesIndexes() {
        $rdb = new Database_Model();
        $cdb = $rdb->GetById(Session::Get(Constants::$SESSION_cdb));
        $table = $cdb->GetName();

        $this->m_sqlMySQL = 'SELECT * FROM table_constraints WHERE table_name=\'' . $table . '\' ORDER BY constraint_type';
        $this->m_sqlOracle = 'SELECT index_name,table_name,uniqueness,status FROM all_indexes WHERE owner=\'' . strtoupper($_SESSION[Constants::$SESSION_ldb]) . '\' ORDER BY table_name';
        // Optionnale dbname
        $dbname = 'information_schema';
        $this->SetInfoDB($dbname);
        // Return All datas fetAll. If more data, PDO return an error. So return just m_result
        return $this->m_result->fetchAll();
    }

    /**
     * Get Views informations
     *  @todo Inform sql request and database Views
     */
    public function GetViews() {
        $this->m_sqlMySQL = 'SELECT * FROM TABLES WHERE table_type = \'SYSTEM VIEW\'';
        $this->m_sqlOracle = 'SELECT view_name,read_only FROM USER_VIEWS';
        // Optionnale dbname
        $dbname = 'information_schema';
        $this->SetInfoDB($dbname);
        // Return All datas fetAll. If more data, PDO return an error. So return just m_result
        return $this->m_result->fetchAll();
    }

    /**
     * Get Methods informations
     *  @todo Inform sql request and database Methods
     */
    public function GetMethods() {
        $this->m_sqlMySQL = 'SELECT name,type FROM proc';
        $this->m_sqlOracle = 'SELECT object_name,object_type FROM USER_OBJECTS WHERE object_type=\'FUNCTION\' OR object_type=\'PROCEDURE\'  OR object_type=\'PACKAGE\' ORDER BY object_name,object_type';
        // Optionnale dbname
        $dbname = 'mysql';
        $this->SetInfoDB($dbname);
        // Return All datas fetAll. If more data, PDO return an error. So return just m_result
        return $this->m_result->fetchAll();
    }

    /**
     * Get Triggers informations
     *  @todo Inform sql request and database Triggers
     */
    public function GetTriggers() {
        $this->m_sqlMySQL = 'SELECT trigger_name,trigger_schema FROM triggers';
        $this->m_sqlOracle = 'SELECT trigger_name,trigger_type,triggering_event,table_name,column_name FROM USER_TRIGGERS';
        // Optionnale dbname
        $dbname = 'information_schema';
        $this->SetInfoDB($dbname);
        // Return All datas fetAll. If more data, PDO return an error. So return just m_result
        return $this->m_result->fetchAll();
    }

    /**
     * Get Developper Scripts informations
     *  @todo Inform sql request and database Developper Scripts
     */
    public function GetScriptsDev() {
        $this->m_sqlMySQL = '';
        $this->m_sqlOracle = '';
        $this->SetInfoDB();
        return $this->m_result;
    }

    /**
     * Connect external Database
     * @param String $dbname
     */
    private function SetInfoDB($dbname = FALSE) {
        if ($this->m_bd == NULL) {
            $rdb = new Database_Model();
            $this->m_bd = $rdb->GetById(Session::Get(Constants::$SESSION_cdb));
            $this->m_bd->SetLogin(Session::Get(Constants::$SESSION_ldb));
            $this->m_bd->SetPassword(Session::Get(Constants::$SESSION_pdb));
        }
        if (strtoupper($this->m_bd->GetType()->GetName()) == strtoupper('mysql') && $dbname != FALSE)
            $this->m_bd->SetName($dbname);

        $this->m_ext = new External_Database($this->m_bd);
        $this->SetRequest();
    }

    /**
     * Prepare SQL request
     */
    private function SetRequest() {
        if (Session::Get(Constants::$SESSION_error) == NULL) {
            if (strtoupper($this->m_bd->GetType()->GetName()) == strtoupper('mysql'))
                $this->m_connect = $this->m_ext->prepare($this->m_sqlMySQL);
            else
                $this->m_connect = $this->m_ext->prepare($this->m_sqlOracle);
        }
        if (strtoupper($this->m_bd->GetType()->GetName()) == strtoupper('mysql') && !empty($this->m_sqlMySQL))
            $this->Execute();
        if (strtoupper($this->m_bd->GetType()->GetName()) == strtoupper('oracle'))
            $this->Execute();
    }

    /**
     * Execute SQL request
     */
    private function Execute() {
        // If no error connect Database
        if (Session::Get(Constants::$SESSION_error) == NULL) {
            $this->m_connect->setFetchMode(PDO::FETCH_ASSOC);
            $this->m_connect->execute();

            // If no error response database
            $chkError = $this->m_connect->errorInfo();
            if ($chkError[2] != NULL) {
                Session::Set(Constants::$SESSION_error, $chkError[2]);
                $this->m_result = NULL;
            } else {
                $this->m_result = $this->m_connect;
            }
        }
    }

    /**
     * DB Extern
     * @var DB
     */
    private $m_ext;
    
    /**
     * Current DB selected
     * @var DB
     */
    private $m_bd;

    /**
     * Result connexion database
     * @var Array 
     */
    private $m_connect;

    /**
     * Result Request SQL
     * @var Array
     */
    private $m_result;

    /**
     * Oracle SQL Request
     * @var String 
     */
    private $m_sqlOracle;

    /**
     * MySQL SQL Request
     * @var String 
     */
    private $m_sqlMySQL;

}